Customer Case Study · Finance · Receivables Reconciliation
The transmission company received POWERGRID disbursement advices as more than a thousand PDF and Excel files, each slightly different. HMC built a Power Query process with custom functions that reads every one, cleans it and stacks it into a single table, and keeps working when the format changes.
Figures indicative; validated metrics available on request.
When a distribution company pays its transmission charges, POWERGRID issues a disbursement advice showing how the payment is split between licensees. To reconcile receipts for its transmission assets, the transmission company needed every advice in one table.
The advices sat in batch folders from DA 3011 to DA 4001. Opening each file and copying its rows would take weeks.
Headers wrapped across lines, page footers and total rows sat inside tables, and numbers mixed Indian and Western commas.
Later batches arrived as Excel instead of PDF, with title rows, serial dates and renamed licensees. Any fixed template would break.
Billing, disbursement ratio, rebate, TDS and net disbursement all had to tie out, file by file, payer by payer.
Built with the transmission company's finance team, in the Excel workbook they already used for reconciliation.
Select a step to see what happened.
We sampled advices from every batch folder and listed every way they differed.
Deliverable: Variability catalogue and target schema.
Every file, whatever its format, ends up in the same twelve columns.
Deliverable: Canonical schema and validation rules.
Each kind of variation got its own small, reusable M function.
fxTransformDA reads one file and routes PDF or Excel.fxCleanHeaders, fxParseINR, fxSplitRTGS, fxMapLicensee, fxFromExcel.Deliverable: Power Query workbook with six custom functions.
The finance team's whole routine became two actions.
Deliverable: One-page guide and walkthrough.
A new variation means one new mapping or function, not a rebuild.
Deliverable: Maintenance notes and mapping tables.
Below are the original screens, then a step-by-step replay of what Power Query does to one advice, and finally the whole folder refreshing at once.
Shown as delivered. Click any screen to enlarge it. One folder name in the query path is pixelated.
Pick a format, then press play or step through. Pink marks dirty data; teal marks what the custom functions fixed. All three formats end in the same clean table.
Press Refresh to see the folder query work through every batch. The router sends PDFs and Excel files to the right function and logs each variation it handles.
Batch names and formats follow the original folders (DA 3204–3330 is not visible in the screenshot and is assumed to be PDF). Row counts and logged variations are illustrative.
The team now spends its time on reconciliation exceptions instead of building the table they reconcile from.
Building the full table from DA 3011 to DA 4001
Share of each issue type fixed vs slipping through
Figures indicative; validated metrics available on request.
Once Power Query can read one advice reliably, a thousand is just a refresh.
A catalogue of the ways files differ became a set of small functions. Each new quirk is one more rule, not a rebuild.
Validation built into the query means reconciliation starts from data the team already trusts.